﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.InternetBanking;
using System.Data;
namespace DataAccess.InternetBanking
{
    public class InternetBankingDAL
    {
        private SQLDataAccess db;
        public InternetBankingDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getInternetBankingList(int langid)
        {
            return db.exeSelect("select * from InternetBanking ca join InternetBankingLang cl on ca.InternetBankingID = cl.InternetBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and cla.LangID = " + langid + "order by ca.InternetBankingID desc");
        }
        public int InsertInternetBanking(InternetBankingModels models)
        {
            return db.exeUpdate("insert into InternetBanking(InternetBankingName,CompanyID,Cost,Interest,Display,UserPost) values (N'" + models.InternetBankingName + "'," + models.CompanyID + "," + models.Cost + "," + models.Interest + ",'" + models.Display + "','" + models.UserPost + "') ");
        }
        public int InsertInternetBankingLang(InternetBankingModels models, int langid)
        {
            return db.exeUpdate("insert into InternetBankingLang values (" + models.InternetBankingID + "," + langid + ",N'" + models.InternetBankingName + "',N'" + models.BenefitContent + "',N'" + models.FurtureContent + "',N'" + models.Description + "',N'" + models.MoreBenefit + "',N'" + models.MoreFee + "',N'" + models.MoreInfo + "')");
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from InternetBanking order by InternetBankingID desc");
        }
        public DataTable GetAllToManager(int langid)
        {
            return db.exeSelect("Select * from InternetBanking sa join InternetBankingLang sl on sa.InternetBankingID = sl.InternetBankingID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on sl.LangID = la.ID where cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }

        public int UpdateInternetBankingLang(InternetBankingModels models, int langid)
        {
            return db.exeUpdate("update InternetBankingLang set InternetBankingName = N'" + models.InternetBankingName + "',BenefitContent=N'" + models.BenefitContent + "',FurtureContent=N'" + models.FurtureContent + "',Description=N'" + models.Description + "',MoreBenefit = N'" + models.MoreBenefit + "',MoreFee = N'" + models.MoreFee + "',MoreInfo = N'" + models.MoreInfo + "' where InternetBankingID = " + models.InternetBankingID + " and LangID =" + langid + "");
        }
        public int UpdateInternetBanking(InternetBankingModels models)
        {
            return db.exeUpdate("update InternetBanking set CompanyID= " + models.CompanyID + ",Cost=" + models.Cost + ",Interest=" + models.Interest + ",Display = '" + models.Display + "',UserUpdate = '" + models.UserUpdate + "',DateUpdate = '" + models.DateUpdate + "' where InternetBankingID = " + models.InternetBankingID + "");
        }
        public int DeleteInternetBanking(int InternetBankingID)
        {
            return db.exeUpdate("Delete from InternetBanking where InternetBankingID = " + InternetBankingID + "");
        }
        public int DeleteInternetBankingLang(int InternetBankingID)
        {
            return db.exeUpdate("Delete from InternetBankingLang where InternetBankingID = " + InternetBankingID + "");
        }

        public DataTable GetInternetBankingByIDToManager(int InternetBankingID, int langid)
        {
            return db.exeSelect("select * from InternetBanking ca join InternetBankingLang cl on ca.InternetBankingID = cl.InternetBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where  ca.InternetBankingID = " + InternetBankingID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public DataTable GetInternetBankingByID(int InternetBankingID, int langid)
        {
            return db.exeSelect("select * from InternetBanking ca join InternetBankingLang cl on ca.InternetBankingID = cl.InternetBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where co.Active = 1 and  ca.InternetBankingID = " + InternetBankingID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public int DeleteInternetBankingApply(int InternetBankingApplyID)
        {
            return db.exeUpdate("Delete from InternetBankingApply where InternetBankingApplyID = " + InternetBankingApplyID + "");
        }
        public int UpdateStatus(InternetBankingApplyModels card)
        {
            return db.exeUpdate("update InternetBankingApply set StatusID= " + card.StatusID + " where InternetBankingApplyID = " + card.InternetBankingApplyID + "");
        }
        public int InternetBankingApply(int InternetBankingID, InternetBankingApplyModels app)
        {
            return db.exeUpdate("insert into InternetBankingApply(InternetBankingID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,CurrentAccount,SavingsDeposits,CreditCard,HomeLoan,CarLoan,PersonalLoan,BusinessLoan,City,ContactTime,StatusID) values (" + InternetBankingID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "','" + app.CurrentAccount + "','" + app.SavingsDeposits + "','" + app.CreditCard + "','" + app.HomeLoan + "','" + app.CarLoan + "','" + app.PersonalLoan + "','" + app.BusinessLoan + "',N'" + app.City + "',N'" + app.ContactTime + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from InternetBankingApply ca join InternetBanking c on ca.InternetBankingID = c.InternetBankingID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.InternetBankingApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from InternetBankingApply ca join [InternetBanking] c on ca.InternetBankingID = c.InternetBankingID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from InternetBankingApply ca join [InternetBanking] c on ca.InternetBankingID = c.InternetBankingID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
        public DataTable getcontractbystatuscount(int statusid)
        {
            return db.exeSelect("select * from InternetBankingApply ca join [InternetBanking] c on ca.InternetBankingID = c.InternetBankingID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.StatusID = " + statusid + " and cl.LangID = 1");
        }
        public DataTable getInternetBankingbyProvider(int CompanyID, int langid)
        {
            return db.exeSelect("select * from InternetBanking ca join InternetBankingLang cl on ca.InternetBankingID = cl.InternetBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " and ca.CompanyID= " + CompanyID + "");
        }

    }
}
